﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace SpringStudio.DAL.Implement
{
    /// <summary>
    /// MSSQL数据库对IDataBase接口的实现
    /// </summary>
    internal class Mssql : IDataBase
    {
        #region SQL命令的预处理

        /// <summary>
        /// SQL命令的预处理
        /// </summary>
        /// <param name="command">SQL命令对象</param>
        /// <param name="connection">数据库连接</param>
        /// <param name="transaction">SQL事务</param>
        /// <param name="commandType">SQL命令类型</param>
        /// <param name="commandText">SQL命令文本</param>
        /// <param name="commandParameters">SQL参数列表</param>
        /// <param name="mustCloseConnection">(返回值)是否必须关闭数据库连接</param>
        private static void PrepareCommand(IDbCommand command, IDbConnection connection, IDbTransaction transaction,
            CommandType commandType, string commandText, IDbDataParameter[] commandParameters, out bool mustCloseConnection)
        {
            ValidationCheck.Command(command);
            ValidationCheck.CommandText(commandText);
            if (connection.State != ConnectionState.Open)
            {
                mustCloseConnection = true;  // 在单一SQL命令中打开的数据库连接，必须执行完之后关闭
                connection.Open();
            }
            else
            {
                mustCloseConnection = false;
            }

            // 数据库事务
            if (transaction != null)
            {
                if (transaction.Connection != null)
                {
                    command.Transaction = transaction;
                }
                else
                {
                    throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
                }
            }

            command.Connection = connection;
            command.CommandText = commandText;
            command.CommandType = commandType;

            // 添加SQL语句参数
            if (commandParameters != null)
            {
                foreach (SqlParameter param in commandParameters)
                {
                    if (param != null)
                    {
                        if ((param.Direction == ParameterDirection.Input || param.Direction == ParameterDirection.InputOutput) && param.Value == null)
                        {
                            param.Value = DBNull.Value;
                        }
                        command.Parameters.Add(param);
                    }
                }
            }
        }

        #endregion

        #region ExecuteDataSet实现的定义

        /// <summary>
        /// ExecuteDataSet实现，序号：0
        /// </summary>
        private DataSet ExecuteDataSet(IDbConnection connection, IDbTransaction transaction, CommandType commandType, string commandText, IDbDataParameter[] commandParameters)
        {
            ValidationCheck.Connection(connection);
            bool mustCloseConnection;
            SqlCommand command = new SqlCommand();
            PrepareCommand(command, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
            using (SqlDataAdapter adapter = new SqlDataAdapter(command))
            {
                DataSet ds = new DataSet();
                try
                {
                    adapter.Fill(ds);
                }
                catch (SqlException ex)
                {
                    SqlExceptionHandler.Handle(ex, commandText, commandParameters);
                }
                finally
                {
                    command.Parameters.Clear();
                    if (mustCloseConnection)
                    {
                        connection.Close();
                    }
                }
                return ds;
            }
        }

        /// <summary>
        /// ExecuteDataSet实现，序号：1
        /// </summary>
        public DataSet ExecuteDataSet(IDbConnection connection, CommandType commandType, string commandText, params IDbDataParameter[] commandParameters)
        {
            return this.ExecuteDataSet(connection, null, commandType, commandText, commandParameters);  // 调用序号0的实现
        }

        /// <summary>
        /// ExecuteDataSet实现，序号：2
        /// </summary>
        public DataSet ExecuteDataSet(IDbConnection connection, CommandType commandType, string commandText)
        {
            return this.ExecuteDataSet(connection, commandType, commandText, null);  // 调用序号1的实现
        }

        /// <summary>
        /// ExecuteDataSet实现，序号：3
        /// </summary>
        public DataSet ExecuteDataSet(string connectionString, CommandType commandType, string commandText, params IDbDataParameter[] commandParameters)
        {
            ValidationCheck.ConnectString(connectionString);
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                return this.ExecuteDataSet(connection, commandType, commandText, commandParameters);  // 调用序号1的实现
            }
        }

        /// <summary>
        /// ExecuteDataSet实现，序号：4
        /// </summary>
        public DataSet ExecuteDataSet(string connectionString, CommandType commandType, string commandText)
        {
            return this.ExecuteDataSet(connectionString, commandType, commandText, null);  // 调用序号3的实现
        }

        /// <summary>
        /// ExecuteDataSet实现，序号：5
        /// </summary>
        public DataSet ExecuteDataSet(IDbTransaction transaction, CommandType commandType, string commandText, params IDbDataParameter[] commandParameters)
        {
            ValidationCheck.Transaction(transaction);
            return this.ExecuteDataSet(transaction.Connection, transaction, commandType, commandText, commandParameters);  // 调用序号0的实现
        }

        /// <summary>
        /// ExecuteDataSet实现，序号：6
        /// </summary>
        public DataSet ExecuteDataSet(IDbTransaction transaction, CommandType commandType, string commandText)
        {
            return this.ExecuteDataSet(transaction, commandType, commandText, null);  // 调用序号5的实现
        }

        #endregion

        #region ExecuteNonQuery实现的定义

        /// <summary>
        /// ExecuteNonQuery实现，序号：0
        /// </summary>
        private int ExecuteNonQuery(IDbConnection connection, IDbTransaction transaction, CommandType commandType, string commandText, IDbDataParameter[] commandParameters)
        {
            ValidationCheck.Connection(connection);
            bool mustCloseConnection;
            SqlCommand command = new SqlCommand();
            PrepareCommand(command, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
            int affectRows = -1;  // 执行SQL语句影响的行数
            try
            {
                affectRows = command.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                SqlExceptionHandler.Handle(ex, commandText, commandParameters);
            }
            finally
            {
                command.Parameters.Clear();
                if (mustCloseConnection)
                {
                    connection.Close();
                }
            }
            return affectRows;
        }

        /// <summary>
        /// ExecuteNonQuery实现，序号：1
        /// </summary>
        public int ExecuteNonQuery(IDbConnection connection, CommandType commandType, string commandText, params IDbDataParameter[] commandParameters)
        {
            return this.ExecuteNonQuery(connection, null, commandType, commandText, commandParameters);  // 调用序号0的实现
        }

        /// <summary>
        /// ExecuteNonQuery实现，序号：2
        /// </summary>
        public int ExecuteNonQuery(IDbConnection connection, CommandType commandType, string commandText)
        {
            return this.ExecuteNonQuery(connection, commandType, commandText, null);  // 调用序号1的实现
        }

        /// <summary>
        /// ExecuteNonQuery实现，序号：3
        /// </summary>
        public int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText, params IDbDataParameter[] commandParameters)
        {
            ValidationCheck.ConnectString(connectionString);
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                return this.ExecuteNonQuery(connection, commandType, commandText, commandParameters);  // 调用序号1的实现
            }
        }

        /// <summary>
        /// ExecuteNonQuery实现，序号：4
        /// </summary>
        public int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText)
        {
            return this.ExecuteNonQuery(connectionString, commandType, commandText, null);  // 调用序号3的实现
        }

        /// <summary>
        /// ExecuteNonQuery实现，序号：5
        /// </summary>
        public int ExecuteNonQuery(IDbTransaction transaction, CommandType commandType, string commandText, params IDbDataParameter[] commandParameters)
        {
            ValidationCheck.Transaction(transaction);
            return this.ExecuteNonQuery(transaction.Connection, transaction, commandType, commandText, commandParameters);  // 调用序号0的实现
        }

        /// <summary>
        /// ExecuteNonQuery实现，序号：6
        /// </summary>
        public int ExecuteNonQuery(IDbTransaction transaction, CommandType commandType, string commandText)
        {
            return this.ExecuteNonQuery(transaction, commandType, commandText, null);  // 调用序号5的实现
        }

        #endregion

        #region Transaction相关实现的定义
        public IDbTransaction GetTransaction(string connectionString)
        {
            ValidationCheck.ConnectString(connectionString);
            SqlConnection connection = new SqlConnection(connectionString);
            connection.Open();
            return connection.BeginTransaction();
        }

        public void CommitTransaction(IDbTransaction transaction)
        {
            ValidationCheck.Transaction(transaction);
            SqlConnection connection = transaction.Connection as SqlConnection;
            using (transaction)
            {
                transaction.Commit();
            }

            if (connection.State != ConnectionState.Closed)
            {
                connection.Close();
            }
        }

        public void RollbackTransaction(IDbTransaction transaction)
        {
            ValidationCheck.Transaction(transaction);
            SqlConnection connection = transaction.Connection as SqlConnection;
            using (transaction)
            {
                transaction.Rollback();
            }

            if (connection.State != ConnectionState.Closed)
            {
                connection.Close();
            }
        }
        #endregion

        #region 新建SQL参数

        public IDbDataParameter NewSqlParameter(string parameterName, object parameterValue, SqlDbType dbType)
        {
            ValidationCheck.ParameterName(parameterName);
            SqlParameter sqlParameter = new SqlParameter(parameterName, parameterValue);
            sqlParameter.SqlDbType = dbType;
            return sqlParameter;
        }

        public IDbDataParameter NewSqlParameter(string parameterName, object parameterValue)
        {
            ValidationCheck.ParameterName(parameterName);
            return new SqlParameter(parameterName, parameterValue);
        }

        #endregion
    }
}
